from typing import List, Any, Union, Dict

from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy import or_, and_, select
from sqlalchemy.ext.asyncio import AsyncSession
from config.logging import logger
from datetime import datetime
from sqlalchemy.engine.row import Row
import re
from sqlalchemy.sql.elements import BinaryExpression
from models.base import Base
from sqlalchemy.future import select
import sqlalchemy
class databaseManager:
    # def __init__(self, db: Session):
    #     self.db = db
    def __init__(self, db: Union[Session, AsyncSession]):
        self.db = db

    @staticmethod
    def paginate_query(query, page: int, limit: int):
        """
        执行分页查询
        :param query: 要查询的基础查询对象
        :param page: 页数
        :param limit: 每页数量
        :return: 查询结果
        """
        # 计算分页偏移量
        offset = (page - 1) * limit
        # 查询数据并分页
        result = query.offset(offset).limit(limit).all()

        return result


    async def async_list_query(self, model: Base, param: dict, fields=None, needRow=False):
        """
        异步列表查询
        """
        if not isinstance(param, dict):
            return False

            # 构建查询条件
        conditions = [getattr(model, key) == value for key, value in param.items()]
        stmt = select(model).filter(sqlalchemy.and_(*conditions))

        if fields:
            entities = [getattr(model, field) for field in fields if hasattr(model, field)]
            if entities:
                stmt = stmt.with_only_columns(*entities)

        try:
            result = await self.db.execute(stmt)
            results = result.scalars().all()

            if needRow:
                return results

            return [result.to_dict() if hasattr(result, 'to_dict') else dict(result) for result in results]

        except Exception as e:
            logger.error(f"列表查询失败，数据库操作出错: {str(e)}")
            return {"error": f"列表查询失败，数据库操作出错: {str(e)}"}

    def detail_query(self, model: Base, param: dict, need=False, fields=None, needRow=False):
        """
        详情查询
        :param need:  需要等值比较，为True
        :param model: 数据库模型
        :param param: 查询参数（字典）
        :param fields: 指定返回的字段列表
        :param needRow: 是否需要返回Row
        :return: 查询结果
        """
        if not isinstance(param, dict):
            # 如果不是字典则返回False
            return False
        query = self.db.query(model)
        # 指定返回的字段
        if fields:
            entities = [getattr(model, field) for field in fields if hasattr(model, field)]
            if entities:
                query = query.with_entities(*entities)

        # 构建查询条件
        conditions: List[BinaryExpression] = []

        # 请注意，filter_by 适用于等值比较。如果您需要进行更复杂的比较（比如范围查询、不等式等），则需要使用 filter 方法，并手动构造条件。
        if need:
            conditions = [getattr(model, key) == value for key, value in param.items()]
            res = query.filter(and_(*conditions)).first()
        else:
            res = query.filter_by(**param).first()  # 使用 filter_by 适用于等值比较，param 应该是字典

        # 加入 deleted_at is None 条件
        if hasattr(model, 'deleted_at'):
            conditions.append(getattr(model, 'deleted_at') == None)

        if needRow:
            logger.info(f"类型{type(res)}")
            return res
        # 如果结果是 Row 对象，则转换为字典
        if needRow is False and isinstance(res, Row):
            return dict(res)
        if res is None:
            return None
        return res.to_dict()

    async def async_detail_query(self, model: Base, param: dict, need=False, fields=None, needRow=False):
        """
        异步详情查询
        :param need:  需要等值比较，为True
        :param model: 数据库模型
        :param param: 查询参数（字典）
        :param fields: 指定返回的字段列表
        :param needRow: 是否需要返回Row
        :return: 查询结果
        """
        if not isinstance(param, dict):
            return False

        # 构建查询条件
        conditions = [getattr(model, key) == value for key, value in param.items()]

        if hasattr(model, 'deleted_at'):
            conditions.append(getattr(model, 'deleted_at') == None)

        query = select(model).filter(and_(*conditions))

        if fields:
            entities = [getattr(model, field) for field in fields if hasattr(model, field)]
            query = query.with_only_columns(entities)

        result = await self.db.execute(query)
        res = result.scalars().first()

        if needRow:
            return res
        if res is None:
            return None
        # 如果需要返回字典
        return {column.name: getattr(res, column.name) for column in res.__table__.columns}

    def detail_update(self, model: Base, param: dict, content: dict):
        """
        更新设备信息
        :param model: 数据库模型类
        :param param: 查询参数（字典）
        :param content: 更新内容（字典）
        :return: 是否成功更新
        """
        condition = [getattr(model, key) == value for key, value in param.items()]
        res = self.db.query(model).filter(and_(*condition)).first()
        if res is None:
            return False

            # 更新对象的字段
        for key, value in content.items():
            if hasattr(res, key):
                setattr(res, key, value)

            # 如果模型有 'updated_at' 字段，更新为当前时间
        if hasattr(res, 'updated_at'):
            setattr(res, 'updated_at', datetime.utcnow())

        try:
            # 提交更改
            self.db.commit()
            return res.to_dict()
        except Exception as e:
            # 如果出现异常，则回滚更改
            self.db.rollback()
            logger.info(f"更新出错{e}")
            return False

    def create_entry(self, model: Base, data: dict) -> Union[Dict[Any, Any], bool]:
        """
        创建新的数据库记录
        :param model: 数据库模型类
        :param data: 新记录的数据（字典）
        :return: 是否成功插入
        """
        # 创建新的模型实例
        new_entry = model(**data)

        try:
            # 将新实例添加到数据库会话并提交
            self.db.add(new_entry)
            self.db.commit()
            self.db.refresh(new_entry)  # 刷新以获取分配的 ID 等
            logger.info({column.name: getattr(new_entry, column.name) for column in new_entry.__table__.columns})
            return {column.name: getattr(new_entry, column.name) for column in new_entry.__table__.columns if
                    column.name not in ['created_at', 'updated_at']}

        except Exception as e:
            # 如果出现异常，则回滚更改
            logger.info(f"{str(e)}")
            self.db.rollback()
            return False

    async def async_detail_update(self, model: Base, param: dict, content: dict) -> Union[Dict[Any, Any], bool]:
        """
        异步更新设备信息
        :param model: 数据库模型类
        :param param: 查询参数（字典）
        :param content: 更新内容（字典）
        :return: 是否成功更新
        """
        async with self.db.begin():
            # 构建查询条件
            condition = [getattr(model, key) == value for key, value in param.items()]

            # 查询并更新对象
            query = select(model).filter(*condition)
            result = await self.db.execute(query)
            res = result.scalars().first()

            if res is None:
                return False

            # 更新对象的字段
            for key, value in content.items():
                if hasattr(res, key):
                    setattr(res, key, value)

            # 如果模型有 'updated_at' 字段，更新为当前时间
            if hasattr(res, 'updated_at'):
                setattr(res, 'updated_at', datetime.utcnow())

            try:
                # 提交更改
                await self.db.commit()
                return {column.name: getattr(res, column.name) for column in res.__table__.columns}
            except Exception as e:
                # 如果出现异常，则回滚更改
                await self.db.rollback()
                logger.info(f"更新出错{e}")
                return False

    async def async_create_entry(self, model: Base, data: dict, asyncDb: AsyncSession) -> Union[Dict[Any, Any], bool]:
        """
        异步创建新的数据库记录
        :param model: 数据库模型类
        :param data: 新记录的数据（字典）
        :return: 是否成功插入
        """
        # 创建新的模型实例
        new_entry = model(**data)

        try:
            # 将新实例添加到数据库会话并提交
            asyncDb.add(new_entry)
            await asyncDb.commit()
            await asyncDb.refresh(new_entry)  # 刷新以获取分配的 ID 等
            return {column.name: getattr(new_entry, column.name) for column in new_entry.__table__.columns}

        except Exception as e:
            # 如果出现异常，则回滚更改
            logger.info(f"{str(e)}")
            self.db.rollback()
            return False
    def delete_records_by_ids(self, model: Base, ids: list):
        """
        通用的删除函数，根据ID列表删除模型记录。
        :param model: 模型类
        :param ids: 要删除的记录的ID列表
        :return: 如果删除成功，则返回True；否则返回False
        """

        # 检查传递的ids是否为列表
        if not isinstance(ids, list):
            # 如果不是列表，则尝试解析为逗号分隔的字符串并转为列表
            try:
                ids = [int(id_str) for id_str in ids.split(",")]
            except (ValueError, AttributeError):
                # 解析失败，返回False表示删除失败
                return False

        if not ids:
            return False  # 如果ID列表为空，则不执行删除操作

        # 使用模型的主键构建条件
        primary_key = model.__table__.primary_key.columns.values()[0]
        query = self.db.query(model).filter(primary_key.in_(ids))

        try:
            # 执行删除操作
            query.delete(synchronize_session=False)

            # 提交更改
            self.db.commit()
            return True
        except Exception as e:
            # 处理删除操作中的异常
            self.db.rollback()
            return False

    def delete_records_by_conditions(self, model: Base, conditions: dict):
        """
        通用的删除函数，根据条件删除模型记录。
        :param model: 模型类
        :param conditions: 查询条件（字典）
        :return: 如果删除成功，则返回True；否则返回False
        """
        if not isinstance(conditions, dict):
            return False

        query = self.db.query(model)
        for field, value in conditions.items():
            query = query.filter(getattr(model, field) == value)

        try:
            # 执行删除操作
            query.delete(synchronize_session=False)

            # 提交更改
            self.db.commit()
            return True
        except Exception as e:
            # 处理删除操作中的异常
            self.db.rollback()
            logger.info(f"删除记录出错: {str(e)}")
            return False

    def is_record_exist(self, model, conditions: dict) -> bool:
        """
        检查是否存在满足给定条件的记录
        :param model: SQLAlchemy模型
        :param conditions: 查询条件字典
        :return: 如果记录存在返回True，否则返回False
        """
        query = self.db.query(model)

        for field, value in conditions.items():
            query = query.filter(getattr(model, field) == value)

        return query.first() is not None

    def commit(self):
        try:
            self.db.commit()
        except Exception as e:
            self.db.rollback()
            raise e

    def list_query(self, model: Base, param: dict, fields=None, needRow=False):
        """
        列表查询
        :param model: 数据库模型
        :param param: 查询参数（字典）
        :param fields: 指定返回的字段列表
        :param needRow: 是否需要返回Row
        :return: 查询结果列表
        """
        if not isinstance(param, dict):
            # 如果不是字典则返回False
            return False
        query = self.db.query(model)
        # 指定返回的字段
        if fields:
            entities = [getattr(model, field) for field in fields if hasattr(model, field)]
            if entities:
                query = query.with_entities(*entities)

        # 构造查询条件
        conditions = [getattr(model, key) == value for key, value in param.items()]
        results = query.filter(and_(*conditions)).all()

        # 处理查询结果
        if needRow:
            return results

        # 如果结果是 Row 对象，则转换为字典
        return [dict(result) if isinstance(result, Row) else result.to_dict() for result in results]

    def snake_to_camel(self, snake_str: str) -> str:
        """
        转换为驼峰风格的字符串 parent_id -> parentId
        """
        components = snake_str.split('_')
        # 将第一个组件保持小写，其余的每个单词首字母大写
        return components[0] + ''.join(x.title() for x in components[1:])

    # def convert_dict_keys(self, d: dict) -> List[dict]:
    #     if isinstance(d, list):
    #         return [self.convert_dict_keys(item) for item in d]
    #     return {self.snake_to_camel(k): v for k, v in d.items()}

    def convert_dict_keys(self, obj):
        """
         转换为驼峰风格的字符串 parent_id -> parentId
        """
        if isinstance(obj, dict):
            new_obj = {}
            for k, v in obj.items():
                new_key = self.snake_to_camel(k)
                new_obj[new_key] = self.convert_dict_keys(v)
            return new_obj
        elif isinstance(obj, list):
            return [self.convert_dict_keys(i) for i in obj]
        elif isinstance(obj, datetime):
            return obj.strftime('%Y-%m-%d %H:%M:%S')
        else:
            return obj

    def camel_to_snake(self, camel_str: str) -> str:
        # 插入下划线之前的大写字母，然后将整个字符串转换为小写
        snake_str = re.sub(r'(?<!^)(?=[A-Z])', '_', camel_str).lower()
        return snake_str

    def snake_convert_dict_keys(self, d: dict) -> dict:
        """
        转换为下划线风格的字典键 parentId -> parent_id
        """
        return {self.camel_to_snake(k): v for k, v in d.items()}


